import os
import json
import pandas as pd


# 读取表格
dfs = []
input_dir = "./13.tree_annotation_to_table"
for index, file in enumerate(os.listdir(input_dir)):
    print(index, "\t", file)
    file_path = os.path.join(input_dir, file)
    df = pd.read_excel(file_path, keep_default_na=False)
    dfs.append(df)
df = pd.concat(dfs)
title = df.columns.tolist()
print(title)
# print(df)

# 读取物种相关名字或id表
#species_info_df = pd.read_csv("./cell_species.csv", header=None)
species_info_df = pd.read_csv("./20250814_cell_species.csv", header=None)   #更新了一下物种信息文件，增加了一些物种信息
species_dict = {}
for row in species_info_df.values.tolist():
    species_dict[row[-1]] = row[0:2]

# for k,v in species_dict.items():
#     print(k,v)
with open("test.json", mode="w", encoding="utf-8") as file:
    json.dump(species_dict, file, indent=4)


# ---------------生成genome_statistic_info表----------------------------------
group_species = df.groupby("species")
species_data = []
for species, group in group_species:
    species_id = species_dict[species]
    print(species_id)
    first_row = group.iloc[0].tolist()
    print(first_row)
    genome_statistic = [int(x) for x in first_row[1:3]]
    genome_statistic_2 = [float(first_row[3])]
    quartile = [round(float(x),2) for x in first_row[14:17]]
    group_cluster = group.groupby(
        ["species","chromosome/scaffold/contif","cluster_name"]
    )
    cluster_number = int(len(group_cluster))
    cluster_gene_number = int(group.shape[0])
    text = ([species] + species_id + genome_statistic + genome_statistic_2 +
            quartile + [cluster_number, cluster_gene_number])
    species_data.append(text)
    # try:

    # except Exception as e:
    #     print(e)

title = [
    "genome", "genome_id", "species", "gene_number", "genome_length",
    "genome_average_gene_density", "quartile_lower", "quartile_median",
    "quartile_upper", "cluster_number", "cluster_gene_number"
]

output_dir = "./16.species_statistics"
os.makedirs(output_dir, exist_ok=True)
df = pd.DataFrame(species_data, columns=title)
df_out_path = os.path.join(output_dir, "genome_statistic_info.csv")
df.to_csv(df_out_path, index=False)

sql = (
    "INSERT INTO genome_statistic_info " +
    str(tuple(title)).replace("'","`") + " VALUES " +
    ",\n".join([str(tuple(x)) for x in species_data]) + ";"
)
sql_out_path = os.path.join(output_dir, "genome_statistic_info.sql")
with open(sql_out_path, "w") as f:
    f.write(sql)

